package itesm.coler.data;

import itesm.coler.models.Problem;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class MySQLProblemDAO implements ProblemDAO {

	Connection connection = null;
	
	
	public Connection getConnection(){
		if (connection == null)
		    connection = MySQLDAOFactory.createConnection();
		return connection;
	}
	
	@Override
	public ArrayList<Problem> findAll() throws DAOException, SQLException {
		// TODO Auto-generated method stub
		PreparedStatement findAll = null;
		ResultSet results = null;
		ArrayList<Problem> problemas = new ArrayList<Problem>();
		Connection connection = null;

		connection = getConnection();
		
		findAll = connection.prepareStatement("select * from problem");
		results = findAll.executeQuery();

		while(results.next()){
			Problem holoProblem = new Problem();
			holoProblem.setProblemId(results.getInt("problemid"));
			holoProblem.setName(results.getString("problemname"));
			holoProblem.setDescription(results.getString("description"));
			holoProblem.setDifficultyLevel(results.getInt("difficultylevel"));
			holoProblem.setAvgDuration(results.getInt("durationavg"));
			holoProblem.setUrlPage(results.getString("pageurl"));
			holoProblem.setActive(results.getBoolean("active"));
			problemas.add(holoProblem);
		}
		connection.close();
		return problemas;
	}


	@Override
	public ArrayList<Problem>findByCourse(String Course) throws DAOException {
		// TODO Auto-generated method stub
		
		PreparedStatement findAll = null;
		ResultSet results = null;
		ArrayList<Problem> problema =  new ArrayList<Problem>();
		Connection connection = null;

		connection = getConnection();
		
		try {
			findAll = connection.prepareStatement("select * from problem where courseid = ?");
			findAll.setString(1, Course);
			results = findAll.executeQuery();
			
			while(results.next()){
				Problem holoProblem = new Problem();
				holoProblem.setName(results.getString("problemname"));
				holoProblem.setProblemId(results.getInt("problemid"));
				holoProblem.setDescription(results.getString("description"));
				holoProblem.setDifficultyLevel(results.getInt("difficultylevel"));
				holoProblem.setAvgDuration(results.getInt("durationavg"));
				holoProblem.setUrlPage(results.getString("pageurl"));
				holoProblem.setActive(results.getBoolean("active"));
				problema.add(holoProblem);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return problema;
	}

	@Override
	public Problem findProblemById(int id) {
		// TODO Auto-generated method stub
		PreparedStatement find = null;
		ResultSet results = null;
		Connection connection = null;
		
		connection = getConnection();
		Problem result = new Problem();
		try {
			find = connection.prepareStatement("select * from problem where problemid = ? ");
			find.setInt(1, id);
			results = find.executeQuery();
			result.setDescription(results.getString("description"));
			result.setProblemId(results.getInt("problemid"));
			result.setAvgDuration(results.getInt("durationavg"));
			result.setDifficultyLevel(results.getInt("difficultylevel"));
			result.setUrlPage(results.getString("pageurl"));
			result.setActive(results.getBoolean("active"));
			result.setName(results.getString("problemname"));
			result.setTotalObjectAvg(results.getInt("totalobjectsavg"));
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return result;
	}

	@Override
	public Problem findProblemByName(String name) {
		// TODO Auto-generated method stub
		PreparedStatement find = null;
		ResultSet results = null;
		Connection connection = null;
		
		connection = getConnection();
		Problem result = new Problem();
		try {
			find = connection.prepareStatement("select * from problem where problemname = ? ");
			find.setString(1, name);
			
			results = find.executeQuery();
			result.setDescription(results.getString("description"));
			result.setAvgDuration(results.getInt("durationavg"));
			result.setDifficultyLevel(results.getInt("difficultylevel"));
			result.setUrlPage(results.getString("pageurl"));
			result.setActive(results.getBoolean("active"));
			result.setProblemId(results.getInt("problemid"));
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return result;
	}
	/**
	 * Esto queda pendiente...
	 */
	@Override
	public void insertProblem(Problem problema) {
		// TODO Auto-generated method stub
		PreparedStatement insert = null;
		Connection connection = null;
		connection =getConnection();
		
		try {
			insert = connection.prepareStatement("insert into problem(problemname,description,difficultylevel,durationavg,totalobjectsavg,pageurl,active,courseid) values(?,?,?,?,?,?,?,?);");
			insert.setString(1,problema.getName());
			insert.setString(2, problema.getDescription());
			insert.setInt(3, problema.getDifficultyLevel());
			insert.setInt(4, problema.getAvgDuration());		
			insert.setInt(5, problema.getTotalObjectAvg());
			insert.setString(6, problema.getUrlPage());
			insert.setInt(7, 1);
			insert.setString(8, problema.getCourse().getCourseId());
			insert.execute();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}


	@Override
	public void modifyProblem(Problem modifier) {
		// TODO Auto-generated method stub
		
		PreparedStatement insert = null;
		Connection connection = null;
		
		connection = getConnection();
		
		try {
			insert = connection.prepareStatement("update problem set problemname = ?, description = ?, difficultylevel = ?, durationavg = ?, totalobjectsavg = 20, pageurl = ?, active = ?, courseid = ? where problemname = ?;");
			insert.setString(1,modifier.getName());
			insert.setString(2, modifier.getDescription());
			insert.setInt(3, modifier.getDifficultyLevel());
			insert.setInt(4, modifier.getAvgDuration());
			insert.setString(5, modifier.getUrlPage());	
			insert.setInt(6, 1);
			insert.setString(8,modifier.getName());
			insert.execute();
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

	@Override
	public void deleteProblem(String deleter) {
		// TODO Auto-generated method stub
		PreparedStatement insert = null;
		Connection connection = null;
		connection =getConnection();
		try {
			insert = connection.prepareStatement("delete from problem where problemname = ?");
			insert.setString(1, deleter);
			insert.execute();
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

	@Override
	public void insertWordtoProblem(Problem problema) {
		// TODO Auto-generated method stub
		PreparedStatement insert = null;
		PreparedStatement select = null;
		Connection connection = null;
		ResultSet results;
		connection =getConnection();
		
		try{
			
			select = connection.prepareStatement("select * from problem where problemname = ?");
			select.setString(1,problema.getName());
			results = select.executeQuery();
			results.last();
			problema.setProblemId(results.getInt("problemid"));
			select.close();
			
			insert = connection.prepareStatement("insert into problemword values(?,?);");
			System.out.println(problema.getGlossary().toString());;
			for(int i = 0; i < problema.getGlossary().size();i++){
				insert.setLong(1, problema.getProblemId());
				insert.setLong(2, problema.getGlossary().get(i).getWordId());
				insert.execute();
			}
			insert.close();
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	
	
	


}
